using System;
using System.Data;
using System.Collections;
using APP.QUANLY.Entity;
using APP.QUANLY.Entity.ConditionForm;

namespace APP.QUANLY.DAL
{
    /// <summary>
    /// Summary description for TEMP_HOADONXUATKHOCHITIET.
     /// </summary>
	public class TEMP_HOADONXUATKHOCHITIETDao
    {
	    private string _SQLSelectByKey;
	    private string _SQLUpdate;
	    private string _SQLInsert;
	    private string _SQLDelete;
	    private string _SQLSelectAll;

        #region Properties

        public IDbConnection _dbConnection;

        public IDbConnection DbConnection
        {
            get { return _dbConnection; }
            set { _dbConnection = value; }
        }

        #endregion
        
        #region Constructor

        public TEMP_HOADONXUATKHOCHITIETDao()
        {
            _dbConnection = null;
		    InitSqlQuery();
        }

        public TEMP_HOADONXUATKHOCHITIETDao(IDbConnection con)
        {
            _dbConnection = con;
            InitSqlQuery();
        }

        #endregion


        private void ToLowerQuery()
        {
            _SQLDelete = _SQLDelete.ToLower();
            _SQLUpdate = _SQLUpdate.ToLower();
            _SQLInsert = _SQLInsert.ToLower();
            _SQLSelectByKey = _SQLSelectByKey.ToLower();
        }

        private void ToUpperQuery()
        {
            _SQLDelete = _SQLDelete.ToUpper();
            _SQLUpdate = _SQLUpdate.ToUpper();
            _SQLInsert = _SQLInsert.ToUpper();
            _SQLSelectByKey = _SQLSelectByKey.ToUpper();
        }

        #region InitSqlQuery
        private void InitSqlQuery()
       {
            string query;

            query = @"SELECT MACHITIETHOADON, MAHOADON, MAKHO, MAMATHANG, SOLUONGSI, SOLUONGLE, DONGIASI, CHIETKHAUTHEOPHANTRAM, CHIETKHAUTHEOTIEN, ISCKPHANTRAM, THANHTIENTRUOCCHIETKHAU_CT, THANHTIENSAUCHIETKHAU_CT, GHICHU, ISDELETE FROM TEMP_HOADONXUATKHOCHITIET WHERE (MACHITIETHOADON = @MACHITIETHOADON)";
	        _SQLSelectByKey = query;

            query = @"UPDATE TEMP_HOADONXUATKHOCHITIET SET MAHOADON = @MAHOADON, MAKHO = @MAKHO, MAMATHANG = @MAMATHANG, SOLUONGSI = @SOLUONGSI, SOLUONGLE = @SOLUONGLE, DONGIASI = @DONGIASI, CHIETKHAUTHEOPHANTRAM = @CHIETKHAUTHEOPHANTRAM, CHIETKHAUTHEOTIEN = @CHIETKHAUTHEOTIEN, ISCKPHANTRAM = @ISCKPHANTRAM, THANHTIENTRUOCCHIETKHAU_CT = @THANHTIENTRUOCCHIETKHAU_CT, THANHTIENSAUCHIETKHAU_CT = @THANHTIENSAUCHIETKHAU_CT, GHICHU = @GHICHU, ISDELETE = @ISDELETE WHERE (MACHITIETHOADON = @MACHITIETHOADON)";
	        _SQLUpdate = query;

            query = @"INSERT INTO TEMP_HOADONXUATKHOCHITIET VALUES (@MACHITIETHOADON, @MAHOADON, @MAKHO, @MAMATHANG, @SOLUONGSI, @SOLUONGLE, @DONGIASI, @CHIETKHAUTHEOPHANTRAM, @CHIETKHAUTHEOTIEN, @ISCKPHANTRAM, @THANHTIENTRUOCCHIETKHAU_CT, @THANHTIENSAUCHIETKHAU_CT, @GHICHU, @ISDELETE)";
	        _SQLInsert = query;

            query = @"DELETE FROM TEMP_HOADONXUATKHOCHITIET WHERE (MACHITIETHOADON = @MACHITIETHOADON)";
	        _SQLDelete = query;

            query = @"SELECT MACHITIETHOADON, MAHOADON, MAKHO, MAMATHANG, SOLUONGSI, SOLUONGLE, DONGIASI, CHIETKHAUTHEOPHANTRAM, CHIETKHAUTHEOTIEN, ISCKPHANTRAM, THANHTIENTRUOCCHIETKHAU_CT, THANHTIENSAUCHIETKHAU_CT, GHICHU, ISDELETE FROM TEMP_HOADONXUATKHOCHITIET";
	        _SQLSelectAll = query;
        }
        #endregion
        public void LoadByCommandText(string commandtext, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = commandtext;

				if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        #region Save & Delete
        private void Save(TEMP_HOADONXUATKHOCHITIET obj, IDbTransaction trans, bool isExisting)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                command = _dbConnection.CreateCommand();

                if (!isExisting) { command.CommandText = _SQLInsert + ";"; }
                else { command.CommandText = _SQLUpdate + ";"; }

                         DalTools.addDbDataParameter(command, "MACHITIETHOADON", obj.MACHITIETHOADON, DbType.String);
                         DalTools.addDbDataParameter(command, "MAHOADON", obj.MAHOADON, DbType.String);
         DalTools.addDbDataParameter(command, "MAKHO", obj.MAKHO, DbType.Guid);
         DalTools.addDbDataParameter(command, "MAMATHANG", obj.MAMATHANG, DbType.Int32);
         DalTools.addDbDataParameter(command, "SOLUONGSI", obj.SOLUONGSI, DbType.Decimal);
         DalTools.addDbDataParameter(command, "SOLUONGLE", obj.SOLUONGLE, DbType.Decimal);
         DalTools.addDbDataParameter(command, "DONGIASI", obj.DONGIASI, DbType.Decimal);
         DalTools.addDbDataParameter(command, "CHIETKHAUTHEOPHANTRAM", obj.CHIETKHAUTHEOPHANTRAM, DbType.Double);
         DalTools.addDbDataParameter(command, "CHIETKHAUTHEOTIEN", obj.CHIETKHAUTHEOTIEN, DbType.Decimal);
         DalTools.addDbDataParameter(command, "ISCKPHANTRAM", obj.ISCKPHANTRAM, DbType.Boolean);
         DalTools.addDbDataParameter(command, "THANHTIENTRUOCCHIETKHAU_CT", obj.THANHTIENTRUOCCHIETKHAU_CT, DbType.Decimal);
         DalTools.addDbDataParameter(command, "THANHTIENSAUCHIETKHAU_CT", obj.THANHTIENSAUCHIETKHAU_CT, DbType.Decimal);
         DalTools.addDbDataParameter(command, "GHICHU", obj.GHICHU, DbType.String);
         DalTools.addDbDataParameter(command, "ISDELETE", obj.ISDELETE, DbType.Boolean);

		        if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Delete(TEMP_HOADONXUATKHOCHITIET obj, IDbTransaction trans)
        {
            if (_dbConnection == null)
            {
                //throw new DalException("Connection has not been set.");
                throw new Exception("Connection has not been set.");
            }

            IDbCommand command;

            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }

                command = _dbConnection.CreateCommand();
                command.CommandText = _SQLDelete + ";";

                         DalTools.addDbDataParameter(command, "MACHITIETHOADON", obj.MACHITIETHOADON, DbType.String);

		        if (trans != null) { command.Transaction = trans; }
                if (command.ExecuteNonQuery() == 0) { throw new Exception("Zero rows were affected."); }
            }
            catch (Exception ex) { throw ex; }
        }

        public void Insert(TEMP_HOADONXUATKHOCHITIET obj, IDbTransaction trans)
        {
            Save(obj, trans, false);
        }
        public void Update(TEMP_HOADONXUATKHOCHITIET obj, IDbTransaction trans)
        {
            Save(obj, trans, true);
        }
        #endregion

        #region Command Execute & Read Properties
     
        private TEMP_HOADONXUATKHOCHITIETList SelectListByCommand(IDbCommand command)
        {
            TEMP_HOADONXUATKHOCHITIETList list = new TEMP_HOADONXUATKHOCHITIETList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {	
				TEMP_HOADONXUATKHOCHITIET obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read()) 
					{ 
						obj = new TEMP_HOADONXUATKHOCHITIET();
						ReadProperties(obj, reader);
						list.Add(obj); 
					}
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
		
        private TEMP_HOADONXUATKHOCHITIETList SelectListByCommand(IDbCommand command, IDbTransaction trans)
        {
            TEMP_HOADONXUATKHOCHITIETList list = new TEMP_HOADONXUATKHOCHITIETList();
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {	
				TEMP_HOADONXUATKHOCHITIET obj;
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
				command.Transaction = trans;
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read()) 
					{ 
						obj = new TEMP_HOADONXUATKHOCHITIET();
						ReadProperties(obj, reader);
						list.Add(obj); 
					}
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        private void DeleteListByCommand(IDbCommand command, IDbTransaction trans)
        {
            if (_dbConnection == null) { throw new Exception("Connection has not been set."); }
            try
            {
                if (_dbConnection.State != ConnectionState.Open) { _dbConnection.Open(); }
                if (trans != null) { command.Transaction = trans; }
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
	
        private void ReadProperties(TEMP_HOADONXUATKHOCHITIET obj, IDataReader reader)
        {
            try
            {
                                if(!(reader["MACHITIETHOADON"] is DBNull))
		{
			obj.MACHITIETHOADON = (string)reader["MACHITIETHOADON"];
		}
                if(!(reader["MAHOADON"] is DBNull))
		{
			obj.MAHOADON = (string)reader["MAHOADON"];
		}
                if(!(reader["MAKHO"] is DBNull))
		{
			obj.MAKHO = (Guid)reader["MAKHO"];
		}
                if(!(reader["MAMATHANG"] is DBNull))
		{
			obj.MAMATHANG = (Int32)reader["MAMATHANG"];
		}
                if(!(reader["SOLUONGSI"] is DBNull))
		{
			obj.SOLUONGSI = (decimal)reader["SOLUONGSI"];
		}
                if(!(reader["SOLUONGLE"] is DBNull))
		{
			obj.SOLUONGLE = (decimal)reader["SOLUONGLE"];
		}
                if(!(reader["DONGIASI"] is DBNull))
		{
			obj.DONGIASI = (decimal)reader["DONGIASI"];
		}
                if(!(reader["CHIETKHAUTHEOPHANTRAM"] is DBNull))
		{
			obj.CHIETKHAUTHEOPHANTRAM = (double)reader["CHIETKHAUTHEOPHANTRAM"];
		}
                if(!(reader["CHIETKHAUTHEOTIEN"] is DBNull))
		{
			obj.CHIETKHAUTHEOTIEN = (decimal)reader["CHIETKHAUTHEOTIEN"];
		}
                if(!(reader["ISCKPHANTRAM"] is DBNull))
		{
			obj.ISCKPHANTRAM = (bool)reader["ISCKPHANTRAM"];
		}
                if(!(reader["THANHTIENTRUOCCHIETKHAU_CT"] is DBNull))
		{
			obj.THANHTIENTRUOCCHIETKHAU_CT = (decimal)reader["THANHTIENTRUOCCHIETKHAU_CT"];
		}
                if(!(reader["THANHTIENSAUCHIETKHAU_CT"] is DBNull))
		{
			obj.THANHTIENSAUCHIETKHAU_CT = (decimal)reader["THANHTIENSAUCHIETKHAU_CT"];
		}
                if(!(reader["GHICHU"] is DBNull))
		{
			obj.GHICHU = (string)reader["GHICHU"];
		}
                if(!(reader["ISDELETE"] is DBNull))
		{
			obj.ISDELETE = (bool)reader["ISDELETE"];
		}
            }
            catch (Exception ex)
            {
                throw new DalException("Failed to read properties from DataReader.", ex);
            }
        }
        #endregion
      
        public TEMP_HOADONXUATKHOCHITIET SelectByKey(string MACHITIETHOADON)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
	                 DalTools.addDbDataParameter(command, "MACHITIETHOADON", MACHITIETHOADON, DbType.String);
      	    TEMP_HOADONXUATKHOCHITIETList list =  SelectListByCommand(command);

            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }

        public TEMP_HOADONXUATKHOCHITIET SelectByKey(string MACHITIETHOADON, IDbTransaction trans)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectByKey;
	                 DalTools.addDbDataParameter(command, "MACHITIETHOADON", MACHITIETHOADON, DbType.String);
      	    TEMP_HOADONXUATKHOCHITIETList list =  SelectListByCommand(command, trans);
			
            if (list.Count == 0) { throw new Exception("No data was returned"); }
            return list[0];
        }
		
        public TEMP_HOADONXUATKHOCHITIETList SelectAll()
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = _SQLSelectAll;
      	    TEMP_HOADONXUATKHOCHITIETList list =  SelectListByCommand(command);
	        return list;
        }

         public TEMP_HOADONXUATKHOCHITIETList SelectByCommand(string Command)
        {
            IDbCommand command  = _dbConnection.CreateCommand();
            command.CommandText = Command;
      	    TEMP_HOADONXUATKHOCHITIETList list =  SelectListByCommand(command);
	        return list;
        }

        public TEMP_HOADONXUATKHOCHITIETList SelectByCondition(TEMP_HOADONXUATKHOCHITIETConditionForm condt)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "SELECT * FROM TEMP_HOADONXUATKHOCHITIET " + GetCondtionQuery(condt) + ";";
            FillParamToCommand(command, condt);
            TEMP_HOADONXUATKHOCHITIETList list = SelectListByCommand(command);
            return list;
        }

        private void DeleteByCondition(TEMP_HOADONXUATKHOCHITIETConditionForm condt, IDbTransaction trans)
        {
            IDbCommand command = _dbConnection.CreateCommand();
            command.CommandText = "DELETE FROM TEMP_HOADONXUATKHOCHITIET " + GetCondtionQuery(condt) + ";";
            FillParamToCommand(command, condt);
            DeleteListByCommand(command, trans);
        }

        #region Make Condition Query & Fill Param from Condition to Command
        private string GetCondtionQuery(TEMP_HOADONXUATKHOCHITIETConditionForm obj)
        {
            if (obj == null) { return ""; }
            string s = "";
            	if (obj.IsSetMACHITIETHOADON)
	{
    	    s += " AND MACHITIETHOADON = @MACHITIETHOADON ";
	}
	if (obj.IsSetMAHOADON)
	{
    	    s += " AND MAHOADON = @MAHOADON ";
	}
	if (obj.IsSetMAKHO)
	{
    	    s += " AND MAKHO = @MAKHO ";
	}
	if (obj.IsSetMAMATHANG)
	{
    	    s += " AND MAMATHANG = @MAMATHANG ";
	}
	if (obj.IsSetSOLUONGSI)
	{
    	    s += " AND SOLUONGSI = @SOLUONGSI ";
	}
	if (obj.IsSetSOLUONGLE)
	{
    	    s += " AND SOLUONGLE = @SOLUONGLE ";
	}
	if (obj.IsSetDONGIASI)
	{
    	    s += " AND DONGIASI = @DONGIASI ";
	}
	if (obj.IsSetCHIETKHAUTHEOPHANTRAM)
	{
    	    s += " AND CHIETKHAUTHEOPHANTRAM = @CHIETKHAUTHEOPHANTRAM ";
	}
	if (obj.IsSetCHIETKHAUTHEOTIEN)
	{
    	    s += " AND CHIETKHAUTHEOTIEN = @CHIETKHAUTHEOTIEN ";
	}
	if (obj.IsSetISCKPHANTRAM)
	{
    	    s += " AND ISCKPHANTRAM = @ISCKPHANTRAM ";
	}
	if (obj.IsSetTHANHTIENTRUOCCHIETKHAU_CT)
	{
    	    s += " AND THANHTIENTRUOCCHIETKHAU_CT = @THANHTIENTRUOCCHIETKHAU_CT ";
	}
	if (obj.IsSetTHANHTIENSAUCHIETKHAU_CT)
	{
    	    s += " AND THANHTIENSAUCHIETKHAU_CT = @THANHTIENSAUCHIETKHAU_CT ";
	}
	if (obj.IsSetGHICHU)
	{
    	    s += " AND GHICHU = @GHICHU ";
	}
	if (obj.IsSetISDELETE)
	{
    	    s += " AND ISDELETE = @ISDELETE ";
	}
            if (s.Length > 0)
            {
                s =" WHERE "+s.Substring(4);
                return s;
            }
            return "";
        }

        private void FillParamToCommand(IDbCommand command, TEMP_HOADONXUATKHOCHITIETConditionForm obj)
        {
	    if (obj == null) { return;}
            	            if (obj.IsSetMACHITIETHOADON)
            {
            	DalTools.addDbDataParameter(command, "MACHITIETHOADON", obj.MACHITIETHOADON, DbType.String); 
            }
            if (obj.IsSetMAHOADON)
            {
            	DalTools.addDbDataParameter(command, "MAHOADON", obj.MAHOADON, DbType.String); 
            }
            if (obj.IsSetMAKHO)
            {
            	DalTools.addDbDataParameter(command, "MAKHO", obj.MAKHO, DbType.Guid); 
            }
            if (obj.IsSetMAMATHANG)
            {
            	DalTools.addDbDataParameter(command, "MAMATHANG", obj.MAMATHANG, DbType.Int32); 
            }
            if (obj.IsSetSOLUONGSI)
            {
            	DalTools.addDbDataParameter(command, "SOLUONGSI", obj.SOLUONGSI, DbType.Decimal); 
            }
            if (obj.IsSetSOLUONGLE)
            {
            	DalTools.addDbDataParameter(command, "SOLUONGLE", obj.SOLUONGLE, DbType.Decimal); 
            }
            if (obj.IsSetDONGIASI)
            {
            	DalTools.addDbDataParameter(command, "DONGIASI", obj.DONGIASI, DbType.Decimal); 
            }
            if (obj.IsSetCHIETKHAUTHEOPHANTRAM)
            {
            	DalTools.addDbDataParameter(command, "CHIETKHAUTHEOPHANTRAM", obj.CHIETKHAUTHEOPHANTRAM, DbType.Double); 
            }
            if (obj.IsSetCHIETKHAUTHEOTIEN)
            {
            	DalTools.addDbDataParameter(command, "CHIETKHAUTHEOTIEN", obj.CHIETKHAUTHEOTIEN, DbType.Decimal); 
            }
            if (obj.IsSetISCKPHANTRAM)
            {
            	DalTools.addDbDataParameter(command, "ISCKPHANTRAM", obj.ISCKPHANTRAM, DbType.Boolean); 
            }
            if (obj.IsSetTHANHTIENTRUOCCHIETKHAU_CT)
            {
            	DalTools.addDbDataParameter(command, "THANHTIENTRUOCCHIETKHAU_CT", obj.THANHTIENTRUOCCHIETKHAU_CT, DbType.Decimal); 
            }
            if (obj.IsSetTHANHTIENSAUCHIETKHAU_CT)
            {
            	DalTools.addDbDataParameter(command, "THANHTIENSAUCHIETKHAU_CT", obj.THANHTIENSAUCHIETKHAU_CT, DbType.Decimal); 
            }
            if (obj.IsSetGHICHU)
            {
            	DalTools.addDbDataParameter(command, "GHICHU", obj.GHICHU, DbType.String); 
            }
            if (obj.IsSetISDELETE)
            {
            	DalTools.addDbDataParameter(command, "ISDELETE", obj.ISDELETE, DbType.Boolean); 
            }
        }
        #endregion
    }
}